import pymysql
from todo import Todo

class TodoManagerMysql:

    @staticmethod
    def get_connection():
        return pymysql.connect(host='192.168.18.130',database='text',user='root',
                               password='root@123ABC',autocommit=True)


    def init_db(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('''CREATE TABLE if not exists todos(
                            todo_id INT  NOT null PRIMARY KEY auto_increment,
                            title VARCHAR(127) NOT null DEFAULT '',
                            completed INT NOT null DEFAULT 0
                            )''')

    def create(self,title:str):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('INSERT INTO todos (title) VALUES(%s);',(title,))
            cursor.execute('select last_insert_id()')
            (todo_id,) = cursor.fetchone()
            return Todo(title,id=todo_id)

    def convert_row_to_object(self, row):
        return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def list_todos(self):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            res = cursor.execute('select * from todos;')
            data = cursor.fetchall()
            list = []
            for row in data:
                list.append(self.convert_row_to_object(row))
            return list

    def get_todo_byid(self, id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            res = cursor.execute('select * from todos where todo_id=%s;',(id,))
            data = cursor.fetchone()
            if data is not None:
                return self.convert_row_to_object(data)

    def mark_completed(self,id):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('UPDATE todos set completed=1 WHERE todo_id=%s;', (id,))
            return self.get_todo_byid(id)

    def delete(self, id):
        with self.get_connection() as conn:
            curson = coon.cursor()
            curson.execute("delete from todos where id=%s;",(id,))
            return self.get_todo_byid(id)

    # def mark_completed(self,id):
    #     with self.get_connection() as conn:
    #         cursor = conn.cursor()
    #         res = cursor.execute('select todo_id from todos where todo_id=%s;',(id,))
    #         data = cursor.fetchone()
    #         for i in data:
    #             cursor.execute('UPDATE todos set completed=1 WHERE todo_id=%s;',(i,))



a = TodoManagerMysql()
a.init_db()
# a.mark_completed(4)
